package cn.bookCity.daoimpl;

import static org.junit.Assert.assertNotNull;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.bookCity.dao.ProductDao;
import cn.bookCity.domain.Orderitem;
import cn.bookCity.domain.Orders;
import cn.bookCity.domain.Products;
import cn.bookCity.util.DataSourceUtil;

public class ProductDaoImpl implements ProductDao {

	@Override
	public int getCount() {
		QueryRunner qr=new QueryRunner(DataSourceUtil.getDataSource());
		try {
			String sql="select count(id) from products";
			Object obj = qr.query(sql, new ScalarHandler());
			return Integer.parseInt(obj.toString());
		}catch (SQLException e) {
		   e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<Products> getPageProducts(int startNo, int pagesize) {
		QueryRunner qr=new QueryRunner(DataSourceUtil.getDataSource());
		try {
			String sql="select * from products limit ?,?";
			return qr.query(sql, new BeanListHandler<>(Products.class),startNo,pagesize);
		}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Products> getAllBooks() {
		QueryRunner qr=new QueryRunner(DataSourceUtil.getDataSource());
		try {
			String sql="select * from products";
			return qr.query(sql,new BeanListHandler<>(Products.class));
		}catch (SQLException e) {
			// TODO: handle exception
		}
		return null;
	}

	@Override
	public Products getProductById(String id) {
		QueryRunner qr=new QueryRunner(DataSourceUtil.getDataSource());
		try {
			String sql="select * from products where id=?";
			return qr.query(sql,new BeanHandler<>(Products.class),id);
		}catch (SQLException e) {
			// TODO: handle exception
		}
		return null;
	}

	@Override
	public void updateProductNum(Orders orders) throws Exception {
		QueryRunner qr=new QueryRunner(DataSourceUtil.getDataSource());
		int result=0;
		int size=orders.getOrderitems().size();
		String sql="update products set pnum=pnum-? where id=? ";
		Object[][] params = new Object[size][2];
		for(int i=0;i<size;i++) {
			Orderitem orderitem=orders.getOrderitems().get(i);
			params[i]=new Object[]{orderitem.getBuynum(),orderitem.getProduct_id()};
		}
		int[] batch=qr.batch(sql, params);
		for (int i = 0; i < batch.length; i++) {
			result += batch[i];
		}
		
		if(result != size) {
			throw new Exception("修改产品表的库存数失败!");
		}
		
		
	}

	@Override
	public List<Products> getProduct(String keywords) {
		QueryRunner qr=new QueryRunner(DataSourceUtil.getDataSource());
		try {
			String key="%"+keywords+"%";
			String sql="select *from products where name like ?";
			return qr.query(sql,new BeanListHandler<>(Products.class),key);
		}catch (SQLException e) {
			// TODO: handle exception
		}
		return null;
	}

	@Override
	public void addProduct(Products products) {
		QueryRunner qr=new QueryRunner(DataSourceUtil.getDataSource());
		try {
			String sql = "insert into products value(?,?,?,?,?,?,?);";
			Object[] params = {products.getId(),products.getName(),products.getPrice(),products.getCategory(),
					products.getPnum(),products.getImgurl(),products.getDescription()};
			qr.update(sql, params);
		}catch (SQLException e) {
			// TODO: handle exception
		}
	}

	@Override
	public void delProduct(String id) {
		
		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
		try {
			String sql = "delete from products where id=?";
			qr.update(sql,id);
		}catch (SQLException e) {
		}
		
	}

}
